package coreservlets;

import java.util.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;

import model.Customer;

@ManagedBean(name="view_order")
public class ViewInfoPageBean
{
   String dbName = "testdatabase";
   String userName = "cromvary";
   String password = "9Kuskup1";
   String hostname = "testdbinstance.crkptu8dcf8x.us-west-2.rds.amazonaws.com";
   String port = "3306";

   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   String DB_URL = "jdbc:mysql://" + hostname + ":" + port + "/" + dbName + "?user=" + userName + "&password=" + password;

   //static final String DB_URL = "jdbc:mysql://localhost:3306/mytestdb";
   //static final String USER = "cromvary";
   //static final String PASS = "9Kuskup1";

   //connect to DB and get customer list
   public List<String> getCustomerList() throws SQLException, ClassNotFoundException
   {
      Connection conn = null;
      Statement stmt = null;
      ResultSet result = null;

      try
      {
    	 String sql = "";

         Class.forName("com.mysql.jdbc.Driver");
         conn = DriverManager.getConnection(DB_URL);
         //conn = DriverManager.getConnection(DB_URL,USER,PASS);
         stmt = conn.createStatement();
	         
         sql = "SELECT * FROM testtable";
         result = stmt.executeQuery(sql);

         List<String> list = new ArrayList<String>();

         while(result.next())
         {
             String queryList = "";
        	 Customer cust = new Customer();

            cust.setCustomerID(result.getLong("customer_id"));
            cust.setName(result.getString("name"));
            cust.setPassword(result.getString("password"));

            queryList = ("ID: " + cust.getCustomerID() + " ; Name: " + cust.getName() + " ; Password: " + cust.getPassword());

            //store all data into a List
            list.add(queryList);

         }

         //STEP 6: Clean-up environment
         result.close();
         stmt.close();
         conn.close();

         return list;

      }
      finally{
          //finally block used to close resources
          try{
             if(stmt!=null)
                stmt.close();
          }catch(SQLException se2){
          }// nothing we can do

          try{
             if(conn!=null)
                conn.close();
          }catch(SQLException se){
             se.printStackTrace();
          }//end finally try
      }
   }

   public String returnToMain()
   {	   
	   return("mainpage");
   }

}